package com.cfs.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Map;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import org.json.JSONArray;
import org.json.JSONException;
import org.json.JSONObject;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.shuohe.service.util.sql.EasyuiServiceImpl;
import com.shuohe.util.db.DbQuerySql;
import com.shuohe.util.easyuiBean.EasyUiDataGrid;
import com.shuohe.util.json.Json;
import com.tyteam.SpacApplication;
import com.tyteam.apps.common.JsonResult;

/**
 * 下拉表单数据操作
 * @author zz
 *
 */
@Controller  
@RequestMapping("/crm/ActionFormSelectUtil/Select/*")  
public class ActionFormSelectUtil
{
	
	@Autowired
    private JdbcTemplate jdbcTemplate;
	
	private void debug(String s)
	{
		System.out.println(s);
	}
	
	private Logger logger = LoggerFactory.getLogger(ActionFormSelectUtil.class);
	
    @Resource
    private EasyuiServiceImpl easyUiDto;
	
	/**
	 * 根据表名新增数据的接口
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="insert.do")  
    public @ResponseBody int insert(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		String jsonStr = request.getParameter("jsonStr");		
		JSONObject obj = new JSONObject(jsonStr);
		String tableName = obj.getString("title"); 
		String value = obj.getString("text"); 
		String value_en = obj.getString("text_en"); 		
		//增加英文字段
		try{
			addField(tableName, "text_en");
		}catch(Exception e){
			System.out.println("字段已存在！");
		}
		String sql = "insert into "+tableName+" (text,text_en) values ("+value+","+value_en+")";
	 	int i=0; 
	    Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        i = stmt.executeUpdate(sql); 
		System.out.println("sql=="+sql);
		System.out.println("iii=="+i);
		stmt.close();
	    return i;
		 
	}
	/**
	 * 根据id,修改数据的接口
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="update.do")  
    public @ResponseBody int update(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String id = request.getParameter("id");
		String jsonStr = request.getParameter("jsonStr");
		
		JSONObject obj = new JSONObject(jsonStr);
		String tableName = obj.getString("title"); 
		String value = obj.getString("text");  
		String value_en = obj.getString("text_en"); 
		//增加英文字段
		try{
			addField(tableName, "text_en");
		}catch(Exception e){
			System.out.println("字段已存在！");
		}
		String sql = "update "+tableName+" set text="+value+",text_en="+value_en+"  where id="+Integer.parseInt(id);
		System.out.println("sql==="+sql);
	 
	 	int i=0; 
	 	Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        i = stmt.executeUpdate(sql); 
		System.out.println("i=="+i);
		stmt.close();
		return i;
		 
	}
	/**
	 * 增加字段（增加英文版字段）
	 * @param table_name 表名
	 * @param textField  字段名称
	 * @return
	 */
	 public boolean addField(String table_name,String textField) {
			
		 String sql = "ALTER TABLE "+table_name+" ADD "+textField+" varchar(255) ";
		 System.out.println(sql);
		try
		{
			jdbcTemplate.execute(sql);
			return true;
		}
		catch(org.springframework.dao.DataAccessException e)
		{
			return false;
		}
	 }
	/** 
	 * 根据id,删除表中数据的接口
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="delete.do")  
    public @ResponseBody int delete(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String id = request.getParameter("id");
		String tableName = request.getParameter("tableName"); 
		int i=0; 
	    String sql = "delete from "+tableName+" where id="+Integer.parseInt(id);
	    Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        i = stmt.executeUpdate(sql); 
        stmt.close();
	    return i;
	}
	/**
	 * 根据表名删除表
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="deleteByTableName.do")  
    public @ResponseBody int deleteByTableName(HttpServletRequest request,HttpServletResponse response) throws Exception{  
    		String tableName = request.getParameter("tableName"); 
    		int i=0; 
    	    String sql = "delete from table_manage where title='"+tableName+"'";
    	    Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        i = stmt.executeUpdate(sql);  	
        stmt.close();
	    return i; 		
	} 
	/**
	 * 根据表名获取表中所有数据
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getByTableName.do")  
    public @ResponseBody EasyUiDataGrid getByTableName(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName");
		  
		String sql = "";
		sql += "SELECT * FROM  " + tableName;
		sql+=DbQuerySql.limit(page, rows);
		
		String sql_count = "select  *  from   "+ tableName ; 
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	/**
	 * 获取所有的表格
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getAllTable.do")  
    public @ResponseBody EasyUiDataGrid getAllTable(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		  
		String sql = "";
		sql += "SELECT * FROM table_manage";
		sql+=DbQuerySql.limit(page, rows);
		
		/*String sql_count = "select count(*) total from  table_manage " ;*/ 
		String sql_count = "select  *  from  table_manage " ; 
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	/**
	 * 根据type 获取所有的子表
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getChildTableByType.do")  
    public @ResponseBody EasyUiDataGrid getChildTableByType(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		  
		String sql = "";
		sql += "SELECT * FROM table_manage where type=1";
		sql+=DbQuerySql.limit(page, rows);
		
		/*String sql_count = "select count(*) total from  table_manage " ;*/ 
		String sql_count = "select  *  from  table_manage where type=1"  ; 
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	
	/**
	 * 根据表名和父表数据record_id
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getDataByRecordId.do")  
    public @ResponseBody EasyUiDataGrid getDataByRecordId(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String tableName = request.getParameter("tableName"); 
		String record_id = request.getParameter("record_id");
		  
		String sql = "";
		sql += "SELECT * FROM "+tableName+" where record_id="+Integer.parseInt(record_id);
		sql+=DbQuerySql.limit(page, rows);
		
		String sql_count = "select  *  from  "+tableName+" where  record_id="+Integer.parseInt(record_id);
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	
	/**
	 * 根据pid获取图表
	 * @param request
	 * @param response
	 * @return
	 * @throws SQLException
	 */
	@RequestMapping(value="getChartsByFormName.do")  
    public @ResponseBody EasyUiDataGrid getChartsByFormName(HttpServletRequest request,HttpServletResponse response) throws SQLException{  
		
		String page = request.getParameter("page"); 
		String rows = request.getParameter("rows"); 
		String formName = request.getParameter("formName");
		  
		String sql = "";
		sql += "SELECT * FROM charts_manage where form_name='" + formName + "'";
		sql+=DbQuerySql.limit(page, rows);
		
		String sql_count = "SELECT * FROM charts_manage where form_name='" + formName + "'";
		
		EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count);
    		return l;
	}
	/**
	 * 新增图表
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="insertCharts.do")  
    public @ResponseBody int insertCharts(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String jsonStr = request.getParameter("jsonStr");
		
		JSONObject obj = new JSONObject(jsonStr);
		String name = obj.getString("name"); 
		String descr = obj.getString("descr"); 
		String type = obj.getString("type"); 
		String formName = obj.getString("formName");
		String x_field = obj.getString("x_field");
		String y_field = obj.getString("y_field");
		String name_x = obj.getString("name_x");
		String name_y = obj.getString("name_y");
		String searchs = obj.getString("searchs");
		String creater = obj.getString("creater");
		String create_date = obj.getString("create_date");
		String sql = "insert into charts_manage (name,descr,type,form_name,x_field,y_field,name_x,name_y,searchs,creater,create_date) values ('"+name+"','"+descr+"','"+type+"','"+formName+"','"+x_field+"','"+y_field+"','"+name_x+"','"+name_y+"','"+searchs+"','"+creater+"','"+create_date+"')";
	 	int i=0; 
	 	System.out.println("sql=="+sql);
	    Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        i = stmt.executeUpdate(sql); 
		System.out.println("iii=="+i);
		stmt.close();
	    return i;
		 
	}
	/**
	 * 修改图表
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="updateCharts.do")  
    public @ResponseBody int updateCharts(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String jsonStr = request.getParameter("jsonStr");
		String id = request.getParameter("id");
		
		JSONObject obj = new JSONObject(jsonStr);
		String name = obj.getString("name"); 
		String descr = obj.getString("descr"); 
		String type = obj.getString("type"); 
		String x_field = obj.getString("x_field");
		String y_field = obj.getString("y_field");
		String name_x = obj.getString("name_x");
		String name_y = obj.getString("name_y");
		String searchs = obj.getString("searchs");
		String sql = "update charts_manage set name='"+name+"',descr='"+descr+"',type='"+type+"',x_field='"+x_field+"',y_field='"+y_field+"',name_x='"+name_x+"',name_y='"+name_y+"',searchs='"+searchs+"' where id="+Integer.parseInt(id);
	 	int i=0; 
	 	System.out.println("sql=="+sql);
	    Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        i = stmt.executeUpdate(sql); 
		System.out.println("iii=="+i);
		stmt.close();
	    return i;
		 
	}
	/** 
	 * 根据id,删除图表的接口
	 * @param request
	 * @param response
	 * @return
	 * @throws Exception
	 */
	@RequestMapping(value="deleteCharts.do")  
    public @ResponseBody int deleteCharts(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String id = request.getParameter("id");
		int i=0; 
	    String sql = "delete from charts_manage where id="+Integer.parseInt(id);
	    Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        i = stmt.executeUpdate(sql);  		
        stmt.close();
	    return i;
	}
	@RequestMapping(value="getChartsById.do")  
    public @ResponseBody EasyUiDataGrid getChartsById(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String id = request.getParameter("id");
		String sql = "select * from charts_manage where id="+Integer.parseInt(id);
	    String sql_count = "select * from charts_manage where id="+Integer.parseInt(id);
	    EasyUiDataGrid l = easyUiDto.getEasyUiDataGrid(sql,sql_count); 		
	    return l;
	}
	@RequestMapping(value="getChartsDataBySearch.do")  
    public @ResponseBody String getChartsDataBySearch(HttpServletRequest request,HttpServletResponse response) throws Exception{  
		
		String formName = request.getParameter("formName");
		String condition = request.getParameter("condition");
		String field = request.getParameter("field");
		String sql = "select "+field+" from "+formName+" where 1=1 "+condition;
		System.out.println("sql=="+sql);
		String json = "";
		Statement stmt = jdbcTemplate.getDataSource().getConnection().createStatement();  
        ResultSet rs = stmt.executeQuery(sql);
        while (rs.next()) {
        	json += rs.getString(1)+",";
        }
        if(json.length()>0) {
        	json = json.substring(0, json.length()-1);
        }
        stmt.close();
	    return json;
	}
}
